import pandas as pd
from DatetimeUtils import choose_day
from ListUtils import anti_join
import time
"""
大盘指数每日指标
"""
# 指数列表
index_basic = ['000001.SH', '000300.SH','000905.SH','000852.SH','399001.SZ','399005.SZ','399006.SZ','399016.SZ']

def deal_append(query_start_, query_end_, engine, pro, index_name):
    """
    通过anti_join函数处理增量数据
    """
    source_list_ = pro.index_daily(ts_code=index_name, start_date=query_start_, end_date=query_end_)
    if len(source_list_) > 0:
        sql_ = 'select b.ts_code, b.trade_date from index_daily b WHERE b.ts_code = "' + index_name + '" and b.trade_date >= ' + query_start_ + ' and b.trade_date <= ' + query_end_
        target_list_ = pd.read_sql(sql_, engine)
        # print('index_dailybasic：(%s)' % target_list_)
        if len(target_list_) == 0:
            source_list_.to_sql('index_daily', con=engine, index=False, index_label=['ts_code', 'trade_date'], if_exists='append')
            print('数据库内未找到数据则全部添加：(%s)' % source_list_)
        else:
            # 数据库内有历史数据则增量添加
            source_list_ = anti_join(source_list_, target_list_, ['ts_code', 'trade_date'])
            if len(source_list_) > 0:
                print('增量：(%s)' % source_list_)
                source_list_.to_sql('index_daily', con=engine, index=False, index_label=['ts_code', 'trade_date'], if_exists='append')
    time.sleep(2)




def insert_history_data_index_daily(engine, pro):
    """
    是否查询并保存历史数据
    """
    insert_history_data_flag = True
    if insert_history_data_flag:
        current_year = choose_day(0).strftime('%Y')
        start_year = 2005  # 起始 2005
        """
        处理历史数据
        """
        for j in range(0, len(index_basic)):
            for i in range(start_year, int(current_year)):
                query_start = '%d' % i + '0101'  # 查询开始
                query_end = '%d' % i + '1231'  # 查询结束
                deal_append(query_start, query_end, engine, pro, index_basic[j])

            """
            处理当年数据
            """
            current_day = choose_day(0).strftime('%Y%m%d')
            year_first_day = current_year + '0101'
            deal_append(year_first_day, current_day, engine, pro, index_basic[j])


def update_current_data_index_daily(engine_, pro_):
    """
    更新当前数据，后续使用定时器
    """
    insert_current_flag = True
    if insert_current_flag:
        for j in range(0, len(index_basic)):
            day_updates = 10 #设定更新30天数据
            current_day = choose_day(0).strftime('%Y%m%d')
            that_day = choose_day(day_updates).strftime('%Y%m%d')
            deal_append(that_day, current_day, engine_, pro_, index_basic[j])


def query_index_daily_data(engine_, index_ts_code, query_start_, query_end_):
    """
    获取相关指数日数据
    :param engine_:
    :param index_ts_code:  index代码
    :param query_start_:  起始时间
    :param query_end_:  结束时间
    :return:
    """
    sql_ = 'select id.* from index_daily id WHERE id.ts_code = "'+ index_ts_code + '" and trade_date >= ' + query_start_ + ' and trade_date <= ' + query_end_ + ' order by trade_date asc'
    df_ = pd.read_sql(sql_, engine_)
    df_['trade_date'] = pd.to_datetime(df_['trade_date'], format='%Y%m%d', errors='ignore')
    return df_


def painter_index(engine_, query_start_, query_end_, plt_):
    """
    指数相关
    :param engine_:
    :param query_start_:
    :param query_end_:
    :return:
    """
    print('------------------- 获取数据开始 -------------------')
    # sh001 = query_index_daily_data(engine_, '000001.SH', query_start_, query_end_)
    sh300 = query_index_daily_data(engine_, '000300.SH', query_start_, query_end_)
    sh905 = query_index_daily_data(engine_, '000905.SH', query_start_, query_end_)
    sh852 = query_index_daily_data(engine_, '000852.SH', query_start_, query_end_)
    # sz001 = query_index_daily_data(engine_, '399001.SZ', query_start_, query_end_)
    # sz005 = query_index_daily_data(engine_, '399005.SZ', query_start_, query_end_)
    # sz006 = query_index_daily_data(engine_, '399006.SZ', query_start_, query_end_)
    # sz016 = query_index_daily_data(engine_, '399016.SZ', query_start_, query_end_)

    print('------------------- 获取数据完成 -------------------')

    print('------------------- 画图开始 -------------------')
    # plt_.figure("指数相关")  # 生成图形后的标题
    plt_.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
    plt_.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

    # 单图多折线
    # x1_ = sh001.loc[:, 'trade_date']  # 点的横坐标
    # k1_ = sh001.loc[:, 'close']  # 线1的纵坐标
    x2_ = sh300.loc[:, 'trade_date']  # 点的横坐标
    k2_ = sh300.loc[:, 'close']  # 线2的纵坐标
    x3_ = sh905.loc[:, 'trade_date']  # 点的横坐标
    k3_ = sh905.loc[:, 'close']  # 线2的纵坐标
    x4_ = sh852.loc[:, 'trade_date']  # 点的横坐标
    k4_ = sh852.loc[:, 'close']  # 线2的纵坐标
    # x5_ = sz001.loc[:, 'trade_date']  # 点的横坐标
    # k5_ = sz001.loc[:, 'close']  # 线2的纵坐标

    # plt.plot(x1_, k1_, color='r', label="上证", linewidth=1)
    plt_.plot(x2_, k2_, color='g', label="沪深300", linewidth=1)
    plt_.plot(x3_, k3_, color='k', label="中证500", linewidth=1)
    plt_.plot(x4_, k4_, color='b', label="中证1000", linewidth=1)
    # plt.plot(x5_, k5_, color='orange', label="深证", linewidth=1)

    plt_.xlabel("时间")  # 横坐标名字
    plt_.ylabel("点位")  # 纵坐标名字
    plt_.axis(['2010', '2022', 0, 16000])
    plt_.tick_params(axis='both', labelsize=8)
    plt_.legend(loc="best")  # 图例
    print('------------------- 画图结束  -------------------')